CREATE OR REPLACE PACKAGE BODY admassist.cust_hsd_attendance as
/*
    Copyright 2009 Highline Public Schools
--
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
--
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
--
    You should have received a copy of the GNU General Public License
    along with This program.  If not, see <http://www.gnu.org/licenses/>.
*/

function absence_type
(
   in_absent_code in varchar2,
   in_reason_code in varchar2,
   in_school number
)
return varchar2
is 
   l_absence_type varchar2(1);
   l_exc_absent_code_invalid exception;
begin
   l_absence_type := null;
   if in_absent_code is null then
      l_absence_type := null;
   else
      if in_absent_code in ('L','A') then
         if in_reason_code is null then
            l_absence_type := 'U';
         else
            select
               decode(ac.authorized
                      ,'Y','A'
                      ,decode(nvl(ac.sub_allocation_code,cp.default_sub_allocation_code)
                             ,cp.excused_sub_allocation_1,'E'
                             ,cp.excused_sub_allocation_2,'E'
                             ,cp.unexcused_sub_allocation_1,'U'
                             ,cp.unexcused_sub_allocation_2,'U'
                             )
                      )
               into l_absence_type
            from 
               admassist.attendance_codes ac
               cross join admassist.company cp 
            where
               ac.school = in_school
               and ac.attendance_code = in_reason_code
               and cp.company_key = aa$sis$get_company_key;
         end if;
      else
         raise l_exc_absent_code_invalid;
      end if;
   end if;
   return(l_absence_type);
end;

function sec_day_course_min_total
(
   in_pupil_number in number,
   in_date date
) 
return number
is
   l_min number;
begin
   select sum(tot_min) into l_min
   from
   (
      select
         max(t.total_minutes) as tot_min
      from admassist.cust_hsd_v_sec_attendances_all t
      where
         t.pupil_number = in_pupil_number
         and t.absent_date = in_date
      group by t.school
   )
   ;
   return nvl(l_min,0);
end;

function sec_att_day_percent
(
   in_pupil_number in number,
   in_absent_code in varchar2,
   in_absence_type in varchar2, --A, U, E, ANY
   in_date in date
)
return number
is 
   l_percent number;
begin
   select
      sum((attp.course_minutes * 100)/sec_day_course_min_total(attp.pupil_number,attp.absent_date)) into l_percent
   from admassist.cust_hsd_v_sec_attendances_all attp
   where
      1=1 
      --and attp.absent_code = 'A'
      and attp.pupil_number = in_pupil_number
      and attp.absent_code = in_absent_code
      and (in_absence_type = 'ANY' or absence_type(attp.absent_code,attp.reason_code,attp.school) = in_absence_type)
      and attp.absent_date = trunc(in_date)
      and attp.total_minutes > 0
      --partial work around eSIS bug:
      and attp.course_minutes <= attp.total_minutes
      ;
      return(nvl(l_percent,0));
end;

function sec_att_day_range_count
(
   in_pupil_number in number,
   in_absence_type in varchar2, --A, U, E, ANY
   in_start_date in date,
   in_end_date in date
)
return number
is 
   l_day_count number;
   l_schedule_missed_percentage number;
   l_start_date date;
   l_end_date date;
begin
   l_start_date := trunc(in_start_date);
   l_end_date := trunc(in_end_date);
   select c.SCHEDULE_MISSED_PERCENTAGE into l_schedule_missed_percentage
   from admassist.company c where c.company_key = aa$sis$get_company_key
   ;
   select
      count(*) into l_day_count
   from
      (
         select
            --daily.pupil_number,
            distinct attp.absent_date
         from admassist.cust_hsd_v_sec_attendances_all attp
         where
            attp.pupil_number = in_pupil_number
            and attp.absent_date between l_start_date and l_end_date
      ) days
   where
      1=1
      and 1 =
         case
            when in_absence_type = 'ANY' then
               case 
                  when sec_att_day_percent(in_pupil_number,'A','ANY',days.absent_date) >= l_schedule_missed_percentage then 1
                  else 0
               end
            when in_absence_type = 'U' then 
               case 
                  when sec_att_day_percent(in_pupil_number,'A','ANY',days.absent_date) >= l_schedule_missed_percentage then
                     case
                        when sec_att_day_percent(in_pupil_number,'A',in_absence_type,days.absent_date) >= 
                           greatest(sec_att_day_percent(in_pupil_number,'A','E',days.absent_date),
                              sec_att_day_percent(in_pupil_number,'A','A',days.absent_date)) then 1
                        else 0
                     end
                  else 0
               end
            when in_absence_type = 'E' then
               case 
                  when sec_att_day_percent(in_pupil_number,'A','ANY',days.absent_date) >= l_schedule_missed_percentage then
                     case
                        when sec_att_day_percent(in_pupil_number,'A',in_absence_type,days.absent_date) > 
                           greatest(sec_att_day_percent(in_pupil_number,'A','U',days.absent_date)+0.1,
                              sec_att_day_percent(in_pupil_number,'A','A',days.absent_date)-0.1) then 1
                        else 0
                     end
                  else 0
               end
            when in_absence_type = 'A' then
               case 
                  when sec_att_day_percent(in_pupil_number,'A','ANY',days.absent_date) >= l_schedule_missed_percentage then
                     case
                        when sec_att_day_percent(in_pupil_number,'A',in_absence_type,days.absent_date) > 
                           greatest(sec_att_day_percent(in_pupil_number,'A','U',days.absent_date)+0.1,
                              sec_att_day_percent(in_pupil_number,'A','E',days.absent_date)+0.1) then 1
                        else 0
                     end
                  else 0
               end
            else 0 --error
         end
         ;
   l_day_count := nvl(l_day_count,0);
   return(l_day_count);
end;

function elem_att_day_range_count
(
   in_pupil_number in number,
   in_absence_type in varchar2,
   in_start_date in date,
   in_end_date in date
)
return number
is 
   l_day_count number;
   l_start_date date;
   l_end_date date;
begin
   --What about cross-enrolled?? see enrolments.homeform_code
   l_start_date := trunc(in_start_date);
   l_end_date := trunc(in_end_date);
   select
      count(sub1.absent_date) into l_day_count
   from
      (
         select
            atte.pupil_number,
            --chs.school,
            --chs.full_half_time,
            atte.absent_date,
            --atte.absent_code_pm,
            --atte.reason_code_pm,
            case 
               when in_absence_type = 'ANY' then 'ANY'
               when 
               (
                  (atte.absent_code = 'A' and absence_type(atte.absent_code,atte.reason_code_am,atte.school) = 'U')
                  or (atte.absent_code_pm = 'A' and absence_type(atte.absent_code_pm,atte.reason_code_pm,atte.school) = 'U')
               )  then 'U'
               when 
               (
                  (atte.absent_code = 'A' and absence_type(atte.absent_code,atte.reason_code_am,atte.school) = 'E')
                  or (atte.absent_code_pm = 'A' and absence_type(atte.absent_code_pm,atte.reason_code_pm,atte.school) = 'E')
               )  then 'E'
               when 
               (
                  (atte.absent_code = 'A' and absence_type(atte.absent_code,atte.reason_code_am,atte.school) = 'A')
                  or (atte.absent_code_pm = 'A' and absence_type(atte.absent_code_pm,atte.reason_code_pm,atte.school) = 'A')
               )  then 'A'
               else null
            end as absence_type
         from 
         (
            select
               admassist.aa$sis$get_school.SCHOOL_YEAR(e.school,e.absent_date) as school_year,
               e.school,
               e.pupil_number,
               e.absent_date,
               e.reason_code_am,
               e.reason_code_pm,
               e.absent_code,
               e.absent_code_pm
            from admassist.elem_attendances e
            UNION ALL
            select
               ea.school_year,
               ea.school,
               ea.pupil_number,
               ea.absent_date,
               ea.reason_code_am,
               ea.reason_code_pm,
               ea.absent_code,
               ea.absent_code_pm
            from admassist.elem_attendances_archive ea
         ) atte
         where
            1=1 
            and atte.pupil_number = in_pupil_number
            and (atte.absent_code = 'A' or atte.absent_code_pm = 'A')
            and atte.absent_date between l_start_date and l_end_date
      ) sub1
   where
      1=1
      and sub1.absence_type = in_absence_type
      ;
   l_day_count := nvl(l_day_count,0);
   return(l_day_count);
end; --elem_attendances_count

function stu_att_day_range_count
(
   in_pupil_number in number,
   in_absence_type in varchar2,
   in_start_date in date,
   in_end_date in date
)
return number
is l_day_count number;
begin
   select 
      elem_att_day_range_count(in_pupil_number, in_absence_type, in_start_date, in_end_date)
      +
      sec_att_day_range_count(in_pupil_number, in_absence_type, in_start_date, in_end_date)
      into l_day_count from dual;
   return(l_day_count);
end;


end; --type
/
